import pymysql


class MySQLPipeline:

    def __init__(self):
        """初始化数据库连接和游标对象"""
        try:
            self.conn = pymysql.connect(
                host='124.220.53.71',
                user='root',
                password='1234',
                port=3306,
                database='data',
            )
            self.cursor = self.conn.cursor()
            print(f"数据库连接成功")
        except pymysql.Error as e:
            print(f"数据库连接错误: {e}")

    def process_item(self, item, spider):
        """
        将数据存储到数据表中，如果数据有重复，则会忽略该插入操作
        :param item:
        :param spider:
        :return:
        """
        sql = "INSERT IGNORE INTO latest_post (text, url, time) VALUES (%s,%s,%s)"
        values = (item["text"], item["url"], item["time"])
        try:
            # 执行SQL语句，将数据插入到数据库中
            self.cursor.execute(sql, values)
            # 提交事务，将数据真正存储到数据库中
            self.conn.commit()
            print('数据存储到mysql中......')
        except pymysql.Error as e:
            print(f"数据存储错误: {e}")
        return item

    def close_spider(self, spider):
        """
        关闭游标和数据库连接
        :param spider:
        :return:
        """
        self.cursor.close()
        self.conn.close()
